﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using DBAccess.DAL;
using DBAccess.Object;

namespace DBAccess.Business
{
    public class SubjectBusiness
    {
        public static bool InsertSubject(Subject subject)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                bool rs = false;
                string sql = "insert into subjects(Title, Lead, Description, Metakey, TotalLike, TotalView, TotalComment, TypeSubject, AuthorID, StatusSubject, CreateDate, PublishDate, Priority, ImagePath, Visible, IconSubject, OriginalSubject, LinkSubject,AdminID) values(@title,@lead,@description,@metakey,@totalLike,@totalView,@totalComment,@typeSubject,@authorID,@status,@createDate,@publishDate,@priority,@imagePath,@visible,@iconSubject,@original,@linkSubject,@adminID)";

                MySqlParameter[] pa = new MySqlParameter[19];
                pa[0] = new MySqlParameter("@title", subject.Title);
                pa[1] = new MySqlParameter("@lead", subject.Lead);
                pa[2] = new MySqlParameter("@description", subject.Content);
                pa[3] = new MySqlParameter("@totalLike", subject.TotalLike);
                pa[4] = new MySqlParameter("@totalView", subject.TotalView);
                pa[5] = new MySqlParameter("@totalComment", subject.TotalComment);
                pa[6] = new MySqlParameter("@typeSubject", subject.TypeSubject);
                pa[7] = new MySqlParameter("@authorID", subject.AuthorID);
                pa[8] = new MySqlParameter("@status", subject.Status);
                pa[9] = new MySqlParameter("@createDate", subject.CreateDate);
                pa[10] = new MySqlParameter("@publishDate", subject.PublishDate);
                pa[11] = new MySqlParameter("@priority", subject.Priority);
                pa[12] = new MySqlParameter("@imagePath", subject.ImagePath);
                pa[13] = new MySqlParameter("@visible", subject.Visible);
                pa[14] = new MySqlParameter("@iconSubject", subject.IconSubject);
                pa[15] = new MySqlParameter("@original", subject.Original);
                pa[16] = new MySqlParameter("@linkSubject", subject.LinkSubject);
                pa[17] = new MySqlParameter("@adminID", subject.AdminID);
                pa[18] = new MySqlParameter("@Metakey", subject.MetaKey);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }
        public static List<Subject> GetListAllSubjectVisible(int visible, int start, int end)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                List<Subject> list = new List<Subject>();
                string sql = "select ad.Username,s.* from subjects s,admin ad where s.AdminID=ad.AdminID and s.Visible=@visible order by publishdate desc limit @start,@end";
                MySqlParameter[] pa = new MySqlParameter[3];
                pa[0] = new MySqlParameter("@visible", visible);
                pa[1] = new MySqlParameter("@start", start);
                pa[2] = new MySqlParameter("@end", end);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Subject sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Content = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());
                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["StatusSubject"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.IconSubject = reader["IconSubject"].ToString();
                    sb.LinkSubject = reader["LinkSubject"].ToString();
                    sb.AuthorName = reader["UserName"].ToString();
                    list.Add(sb);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static List<Subject> GetListAllSubjectVisibleMostLike(int visible, int start, int end)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                List<Subject> list = new List<Subject>();
                string sql = "select ad.Username,s.* from subjects s,admin ad where s.AdminID=ad.AdminID and s.Visible=@visible order by TotalLike desc limit @start,@end";
                MySqlParameter[] pa = new MySqlParameter[3];
                pa[0] = new MySqlParameter("@visible", visible);
                pa[1] = new MySqlParameter("@start", start);
                pa[2] = new MySqlParameter("@end", end);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Subject sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Content = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());
                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["StatusSubject"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.IconSubject = reader["IconSubject"].ToString();
                    sb.LinkSubject = reader["LinkSubject"].ToString();
                    sb.AuthorName = reader["UserName"].ToString();
                    list.Add(sb);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static List<Subject> GetListAllSubjectVisibleMostView(int visible, int start, int end)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                List<Subject> list = new List<Subject>();
                string sql = "select ad.Username,s.* from subjects s,admin ad where s.AdminID=ad.AdminID and s.Visible=@visible order by TotalView desc limit @start,@end";
                MySqlParameter[] pa = new MySqlParameter[3];
                pa[0] = new MySqlParameter("@visible", visible);
                pa[1] = new MySqlParameter("@start", start);
                pa[2] = new MySqlParameter("@end", end);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Subject sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Content = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());
                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["StatusSubject"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.IconSubject = reader["IconSubject"].ToString();
                    sb.LinkSubject = reader["LinkSubject"].ToString();
                    sb.AuthorName = reader["UserName"].ToString();
                    list.Add(sb);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }


        public static List<Subject> GetListAllSubjectVideosVisible(int visible, int typeSubject, int start, int end)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                List<Subject> list = new List<Subject>();
                string sql = "select ad.Username,s.* from subjects s,admin ad where s.AdminID=ad.AdminID and s.Visible=@visible and TypeSubject=@typeSubject order by publishdate desc limit @start,@end";
                MySqlParameter[] pa = new MySqlParameter[4];
                pa[0] = new MySqlParameter("@visible", visible);
                pa[1] = new MySqlParameter("@start", start);
                pa[2] = new MySqlParameter("@end", end);
                pa[3] = new MySqlParameter("@typeSubject", typeSubject);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Subject sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Content = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());
                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["StatusSubject"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.IconSubject = reader["IconSubject"].ToString();
                    sb.LinkSubject = reader["LinkSubject"].ToString();
                    sb.AuthorName = reader["UserName"].ToString();
                    list.Add(sb);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static List<Subject> GetListAllSubjectVideosVisibleMostView(int visible, int typeSubject, int start, int end)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                List<Subject> list = new List<Subject>();
                string sql = "select ad.Username,s.* from subjects s,admin ad where s.AdminID=ad.AdminID and s.Visible=@visible and TypeSubject=@typeSubject order by TotalView desc limit @start,@end";
                MySqlParameter[] pa = new MySqlParameter[4];
                pa[0] = new MySqlParameter("@visible", visible);
                pa[1] = new MySqlParameter("@start", start);
                pa[2] = new MySqlParameter("@end", end);
                pa[3] = new MySqlParameter("@typeSubject", typeSubject);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Subject sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Content = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());
                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["StatusSubject"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.IconSubject = reader["IconSubject"].ToString();
                    sb.LinkSubject = reader["LinkSubject"].ToString();
                    sb.AuthorName = reader["UserName"].ToString();
                    list.Add(sb);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static List<Subject> GetListAllSubjectImageVisibleMostView(int visible, int typeSubject, int start, int end)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                List<Subject> list = new List<Subject>();
                string sql = "select ad.Username,s.* from subjects s,admin ad where s.AdminID=ad.AdminID and s.Visible=@visible and TypeSubject=@typeSubject order by TotalView desc limit @start,@end";
                MySqlParameter[] pa = new MySqlParameter[4];
                pa[0] = new MySqlParameter("@visible", visible);
                pa[1] = new MySqlParameter("@start", start);
                pa[2] = new MySqlParameter("@end", end);
                pa[3] = new MySqlParameter("@typeSubject", typeSubject);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Subject sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Content = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());
                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["StatusSubject"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.IconSubject = reader["IconSubject"].ToString();
                    sb.LinkSubject = reader["LinkSubject"].ToString();
                    sb.AuthorName = reader["UserName"].ToString();
                    list.Add(sb);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }


        public static Subject GetSubjectDetail(int subjectID)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                Subject sb = null;
                string sql = "select ad.UserName,s.* from subjects as s,admin as ad where ID=@subjectID and s.AdminID=ad.AdminID";
                MySqlParameter[] pa = new MySqlParameter[1];
                pa[0] = new MySqlParameter("@subjectID", subjectID);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                MySqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Content = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());

                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["StatusSubject"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.IconSubject = reader["IconSubject"].ToString();
                    sb.LinkSubject = reader["LinkSubject"].ToString();
                    sb.AuthorID = int.Parse(reader["AuthorID"].ToString());
                    sb.AuthorName = reader["UserName"].ToString();
                }
                return sb;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool UpdateViewSubject(int subjectID)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                string sql = "update subjects set TotalView=TotalView+1 where ID=@id";
                MySqlParameter[] pa = new MySqlParameter[1];
                pa[0] = new MySqlParameter("@id", subjectID);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool UpdateCommentSubject(int subjectID)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                string sql = "update subjects set TotalComment=TotalComment+1 where ID=@id";
                MySqlParameter[] pa = new MySqlParameter[1];
                pa[0] = new MySqlParameter("@id", subjectID);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool UpdateLikeSubject(int subjectID)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                string sql = "update subjects set TotalLike=TotalLike+1 where ID=@id";
                MySqlParameter[] pa = new MySqlParameter[1];
                pa[0] = new MySqlParameter("@id", subjectID);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool UpdateUnLikeSubject(int subjectID)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                string sql = "update subjects set TotalLike=TotalLike-1 where ID=@id";
                MySqlParameter[] pa = new MySqlParameter[1];
                pa[0] = new MySqlParameter("@id", subjectID);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool InsertSubjectBuild(SubjectBuild build)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                string sql = "insert into subjectbuild(CriteriaID, SubjectID, CreateDate, Priority, Status) values(@criterialID,@subjectID,@createDate,@priority,@status)";
                MySqlParameter[] pa = new MySqlParameter[5];
                pa[0] = new MySqlParameter("@criterialID", build.CriteriaID);
                pa[1] = new MySqlParameter("@subjectID", build.SubjectID);
                pa[2] = new MySqlParameter("@createDate", build.CreateDate);
                pa[3] = new MySqlParameter("@priority", build.Priority);
                pa[4] = new MySqlParameter("@status", build.Status);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool RemoveSubjectBuild(int criterialID, int subjectID)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                string sql = "delete subjectbuild where CriteriaID=@criterialID and SubjectID=@subjectID";
                MySqlParameter[] pa = new MySqlParameter[2];
                pa[0] = new MySqlParameter("@criterialID", criterialID);
                pa[1] = new MySqlParameter("@subjectID", subjectID);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool LockAndUnlockSubject(int subjectID, int visible)
        {
            DatabaseClass db = new DatabaseClass();
            try
            {
                string sql = "update subjects set Visible=@visible where ID=@subjectID";
                MySqlParameter[] pa = new MySqlParameter[2];
                pa[0] = new MySqlParameter("@visible", visible);
                pa[1] = new MySqlParameter("@subjectID", subjectID);
                MySqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }
    }
}
